﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for FeeLevelDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class MUC_PHI_DAO
    {
        private const string SQL_SELECT_ALL = "select * from MUC_PHI";
        private const string SQL_SELECT_OBJECT_FEELEVEL = "select MA_MUC_PHI, SO_LAN_THANH_TOAN,TONG_TIEN, THOI_HAN, MA_DOI_TUONG from MUC_PHI  where MA_DOI_TUONG = @MA_DOI_TUONG";
        private const string SQL_INSERT_FEE = "insert into MUC_PHI(SO_LAN_THANH_TOAN, TONG_TIEN,THOI_HAN, MA_DOI_TUONG) values(@SO_LAN_THANH_TOAN, @TONG_TIEN, @THOI_HAN, @MA_DOI_TUONG) ";
        private const string SQL_UPDATE_FEE = "update MUC_PHI set SO_LAN_THANH_TOAN = @SO_LAN_THANH_TOAN, TONG_TIEN = @TONG_TIEN, THOI_HAN = @THOI_HAN WHERE MA_MUC_PHI = @MA_MUC_PHI";
        private const string SQL_DELETE_FEE = "delete from MUC_PHI where MA_MUC_PHI = @MA_MUC_PHI";
        private const string SQL_SELECT_FEELEVEL_BY_ID = "select * from MUC_PHI where MA_MUC_PHI=@MA_MUC_PHI";
        // Parameters
        private const string PARM_FEE_LEVEL_ID = "@MA_MUC_PHI";
        private const string PARM_FEE_PAID_TIME = "@SO_LAN_THANH_TOAN";
        private const string PARM_FEE_TOTAL = "@TONG_TIEN";
        private const string PARM_FEE_THOI_HAN = "@THOI_HAN";
        private const string PARM_OBJECT_ID = "@MA_DOI_TUONG";

        #region Public Methods
        public IList<MUC_PHI> GetAll()
        {
            IList<MUC_PHI> levels = new List<MUC_PHI>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    MUC_PHI level = new MUC_PHI();
                    level.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    level.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));
                    level.TONG_TIEN = reader.GetFloat(reader.GetOrdinal("TONG_TIEN"));
                    level.THOI_HAN = reader.GetInt32(reader.GetOrdinal("THOI_HAN"));
                    levels.Add(level);
                }
            }
            return levels;
        }

        public Boolean Insert(MUC_PHI level)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_FEE_PAID_TIME, SqlDbType.Int),
											 new SqlParameter(PARM_FEE_TOTAL, SqlDbType.Float),
                                             new SqlParameter(PARM_FEE_THOI_HAN, SqlDbType.Int),
                                             new SqlParameter(PARM_OBJECT_ID, SqlDbType.Int)};
                parms[0].Value = level.SO_LAN_THANH_TOAN;
                parms[1].Value = level.TONG_TIEN;
                parms[2].Value = level.THOI_HAN;
                parms[3].Value = level.DOI_TUONG.MA_DOI_TUONG;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT_FEE, parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(MUC_PHI level)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_FEE_PAID_TIME, SqlDbType.Int),
											 new SqlParameter(PARM_FEE_TOTAL, SqlDbType.Float),
                                             new SqlParameter(PARM_FEE_THOI_HAN, SqlDbType.Int),
                                             new SqlParameter(PARM_FEE_LEVEL_ID, SqlDbType.Int)};
            parms[0].Value = level.SO_LAN_THANH_TOAN;
            parms[1].Value = level.TONG_TIEN;
            parms[2].Value = level.THOI_HAN;
            parms[3].Value = level.MA_MUC_PHI;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE_FEE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int MA_MUC_PHI)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_FEE_LEVEL_ID, SqlDbType.Int) };
            parms[0].Value = MA_MUC_PHI;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_FEE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public IList<MUC_PHI> SelectByObjectId(int id)
        {
           IList<MUC_PHI> list=new List<MUC_PHI>();
            SqlParameter[] parms;
        parms = new SqlParameter[]{
                new SqlParameter(PARM_OBJECT_ID,SqlDbType.Int)};
        parms[0].Value = id;
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_OBJECT_FEELEVEL, parms))
        {
            while (reader.Read())
            {
 QuanLyHocPhi.Entity.MUC_PHI obj = new QuanLyHocPhi.Entity.MUC_PHI();
                obj.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                obj.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));
                obj.TONG_TIEN = (float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));
                obj.THOI_HAN = reader.GetInt32(reader.GetOrdinal("THOI_HAN"));
                obj.DOI_TUONG = new DOI_TUONG();
                obj.DOI_TUONG.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                list.Add(obj);
            }
            return list;
        }

        }

        public MUC_PHI GetFeeLevelById(int id)
        {
           QuanLyHocPhi.Entity.MUC_PHI obj = new QuanLyHocPhi.Entity.MUC_PHI();
            SqlParameter[] parms;
            parms = new SqlParameter[]{
                new SqlParameter(PARM_FEE_LEVEL_ID,SqlDbType.Int)};
            parms[0].Value = id;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_FEELEVEL_BY_ID, parms))
            {
                while (reader.Read())
                {
                    
                    obj.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    obj.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));
                    obj.TONG_TIEN = (float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));
                    obj.THOI_HAN = reader.GetInt32(reader.GetOrdinal("THOI_HAN"));
                    obj.DOI_TUONG = new DOI_TUONG();
                    obj.DOI_TUONG.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    //list.Add(obj);
                }
                return obj;
            }

        }
        #endregion
    }
}